package com.book.dao;

import com.book.model.Book;
import com.book.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDAO {
    // ...

    /**
     * 得到所有书
     *
     * @return {@link List}<{@link Book}>
     */
    public List<Book> getAllBooks() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Book> books = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM book";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setCategory(rs.getString("category"));
                book.setPrice(rs.getDouble("price"));
                book.setPublisher(rs.getString("publisher"));
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }

        return books;
    }

    /**
     * 添加书
     *
     * @param book 书
     */
    public void addBook(Book book) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO book (name, category, price, publisher) VALUES (?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, book.getName());
            stmt.setString(2, book.getCategory());
            stmt.setDouble(3, book.getPrice());
            stmt.setString(4, book.getPublisher());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }

    /**
     * 更新书
     *
     * @param book 书
     */
    public void updateBook(Book book) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE book SET name = ?, category = ?, price = ?, publisher = ? WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, book.getName());
            stmt.setString(2, book.getCategory());
            stmt.setDouble(3, book.getPrice());
            stmt.setString(4, book.getPublisher());
            stmt.setInt(5, book.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }

    /**
     * 删除书
     *
     * @param bookId 书id
     */
    public void deleteBook(int bookId) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM book WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, bookId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }
    }

    /**
     * 通过id获取书
     *
     * @param bookId 书id
     * @return {@link Book}
     */
    public Book getBookById(int bookId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Book book = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM book WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, bookId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                book = new Book();
                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setCategory(rs.getString("category"));
                book.setPrice(rs.getDouble("price"));
                book.setPublisher(rs.getString("publisher"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closeStatement(stmt);
            DBUtil.closeConnection(conn);
        }

        return book;
    }

    /**
     * 搜索书籍
     *
     * @param keyword 关键字
     * @return {@link List}<{@link Book}>
     */
    public List<Book> searchBooks(String keyword) {
        if (keyword == null) {
            return getAllBooks();
        } else {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            List<Book> books = new ArrayList<>();

            try {
                conn = DBUtil.getConnection();
                String sql = "SELECT * FROM book\n" +
                        "WHERE name LIKE '%+" + keyword + "%' OR category LIKE '%" + keyword + "%' OR publisher LIKE '%" + keyword + "%';";
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();

                while (rs.next()) {
                    Book book = new Book();
                    book.setId(rs.getInt("id"));
                    book.setName(rs.getString("name"));
                    book.setCategory(rs.getString("category"));
                    book.setPrice(rs.getDouble("price"));
                    book.setPublisher(rs.getString("publisher"));
                    books.add(book);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.closeResultSet(rs);
                DBUtil.closeStatement(stmt);
                DBUtil.closeConnection(conn);
            }

            return books;
        }
    }

    // ...
}